

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;


public class test
{
    private static String dbURL = "jdbc:derby://localhost:1527/asd;create=true;user=app;password=123";
    private static String tableName = "PUBLISHER";	// change the table name in uppercase letter (wen)
    // jdbc Connection
    private static Connection conn = null;
    private static Statement stmt = null;

    public static void main(String[] args)
    {
        createConnection();
        
        createTable();
        
       
        
        insertData("PUB0001", "Pearson Malaysia Sdn Bhd",  "Lot 2 Jln Templer, 46050 Petaling Jaya, Selangor, Malaysia",  
        		   "603-78012000","603-77831906",  "info@person.com.my",  "Michael Pear", "017-3019601");
        
        insertData("PUB0002", "Pelangi Malaysia Sdn Bhd",  "Bt 14 Jln Sungai Besi, 46030 Putrajaya, Selangor, Malaysia",  
     		   	   "603-89269070","603-89269080",  "info@pelangi.com.my",  "Andrew Pelangi", "017-6932002");
        
        insertData("PUB0003", "Oxford Fajar Sdn Bhd",  "No.4 Jln Pemaju, 40150 Shah Alam, Selangor, Malaysia",  
     		   	   "603-56294005","603-56294006",  "info@oxford_fajar.com.my",  "Johnson Oxford", "017-3019603");
        
        insertData("PUB0004", "Cerdik Publications Sdn Bhd",  "39, Jln Nilam 1/2, Subang Square, 40000 Shah Alam, Selangor",  
  		   	       "603-56379044","603-56379043",  "info@cerdik.com.my",  "Jonathan Cerdik", "017-3019604");
        
        insertData("PUB0005", "Pan Asia Publishing PTE LTD",  "Lot 2 Jln Klang Lama, 46050 Petaling Jaya, Selangor, Malaysia",  
  		   	       "603-63161788","603-63161789",  "info@panasia.com.my",  "Thomas Pan", "017-3019605");
        
        insertData("PUB0006", "Eastview Publication Sdn Bhd",  "Lot 2 Jln Templer, 46050 Petaling Jaya, Selangor, Malaysia",  
  		   	       "603-89438866","603-89435675",  "info@eastview.com.my",  "Dustin East", "017-3019606");
        
        insertData("PUB0007", "Penerbit Ilmu Bakti Sdn Bhd",  "No 10, Jln Industri Usj 1/4, 47600 Subang Jaya, Selangor, Malaysia",  
  		   	       "603-80237461","603-80237462",  "info@ilmubakti.com.my",  "Emily Ilmu", "017-3019607");
        
        insertData("PUB0008", "Aneka Publication Sdn Bhd",  "No 3, Jln Sri Sarawak 20 B, 41200 Klang, Selangor, Malaysia",  
  		   	       "603-33237043","603-33231672",  "info@aneka.com.my",  "Wilson Aneka", "017-3019608");
        
        insertData("PUB0009", "Edukid Distributors Sdn Bhd",  "59 Jln 3/10B Taman Spring, 68100 Kuala Lumpur,Malaysia",  
  		   	       "603-61873556","603-61873557",  "info@edukid.com.my",  "Henry Edukid", "017-3019609");
        
        insertData("PUB0010", "Professional Engineering Publishing",  "1 Bridcage Walk,London, SW1H9JJ, UK",  
  		   	       "603-79731300","603-79736844",  "info@pep.com.my",  "Mherwin PEP", "017-3019610");
     

        
        

        //DisplayData();
        shutdown();
    }
    
    
    //  to establish connection
    private static void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL); 
        }
        catch (Exception except)
        {
            except.printStackTrace();
        }
    }
    
    
    
    // to create table
    private static void createTable()
    {
    	try 
    	{ 
    		Statement stmt = conn.createStatement(); 
    		// Create table called my_table 
    		String sql = "create table "+ tableName + 
    					 "("+
    					 	"PUB_ID 			VARCHAR(7)	,"		+
    					 	"PUB_NAME 			CHAR(40)	,"		+
    					 	"PUB_ADDRESS 		CHAR(80)	,"		+
    					 	"PUB_TEL 			VARCHAR(12)	,"		+
    					 	"PUB_FAX 			VARCHAR(12)	,"		+
    					 	"PUB_EMAIL 			VARCHAR(25)	,"		+
    					 	"PUB_PERSONNAME 	CHAR(20)	,"		+
    					 	"PUB_PERSONPHONE 	VARCHAR(11)	 "		+
    					 ")";
    		
    		stmt.executeUpdate(sql); 
    	} 
    	catch (SQLException e) 
    	{ 
    		
    	} 
    	
    }
    
    // to insert data
    private static void insertData(String id, String name, String address, String tel,
    							   String fax, String email, String person, String phone)
    {
        try
        {
            stmt = conn.createStatement();
            
            
            stmt.execute("insert into " + tableName +" values " +
            		     	"("	+
            		     		"'"	+	id			+ 	  "',"+
            		     		"'"	+	name		+ 	  "',"+
            		     		"'"	+	address		+ 	  "',"+
            		     		"'"	+	tel			+ 	  "',"+
            		     		"'"	+	fax			+ 	  "',"+
            		     		"'"	+	email		+ 	  "',"+
            		     		"'"	+	person		+ 	  "',"+
            		     		"'"	+	phone		+ 	  "'"+
            		     	")"	
            		     );
            
            

            
            stmt.close();

    


        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
    }
    
    
    
    // to display the table
    /*
    private static void displayData()
    {
        try
        {
            stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("select * from " + tableName);
            ResultSetMetaData rsmd = results.getMetaData();
            int numberCols = rsmd.getColumnCount();
            for (int i=1; i<=numberCols; i++)
            {
                //print Column Names
                System.out.print(rsmd.getColumnLabel(i)+"\t\t");  
            }

            System.out.println("\n-------------------------------------------------");

            while(results.next())
            {
                double id = results.getInt(1);

                System.out.println(id );
            }
            results.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
    }
    */
    
    
    // to shut down the connection
    private static void shutdown()
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }           
        }
        catch (SQLException sqlExcept)
        {
            
        }

    }
}
